 package com.esdk.sql;

 import com.alibaba.fastjson.JSONArray;
 import com.esdk.esdk;
 import com.esdk.sql.orm.*;
 import com.esdk.utils.*;

 import java.sql.*;
 import java.util.ArrayList;
 import java.util.Date;
 import java.util.List;
 import java.util.Map;

 /***
 * @author 范飞宇
 * @since 2005.1.1
 */
public class Select implements ISelect,IPageSelect,AutoCloseable{
  protected Columns columns;
  protected Froms froms;
  protected Wheres wheres;
  protected OrderBys orderbys;
  protected GroupBys groupbys;
  private int offset=-1,limit=-1;
	private Connection _conn;
  private StringBuilder pstmtSQL;
  private boolean isDistinct;
  private boolean isFormatSql=true;
  private ResultSet _resultset;
  private Object[] parameters;
	private boolean _showSql=SQLAssistant.IsShowSql;
  protected boolean useSessionCache=false;
  private boolean isCachable=esdk.prop.getBoolean("db_cachable",false);
	protected int useCacheSec=Constant.ResetUseCacheSec;
  private SqlDialect sqlDialect=SQLAssistant.defaultDialect;
  private ISelect originSelect = null;
  private int retryTimes=0, retryIntervalMs=1000;
  private long executeConcumingTime;
  private boolean isShowFullSql=false; //延迟到全部数据获取之后才打印sql日志

  public Select() {
    froms=new Froms();
    columns=new Columns();
    wheres=new Wheres();
    orderbys=new OrderBys();
    groupbys=new GroupBys();
    pstmtSQL=new StringBuilder();
  }

  public Select(Connection conn) {
  	this();
    setConnection(conn);
  }

  public Select(Table t,Connection conn) {
    this(conn);
    setTable(t);
  }

  public Select(Connection conn,boolean isTop) {
  	this(conn);
  	if(isTop)
  		setTop(SQLAssistant.DefaultSelectLimit);
  }

  public Select(String tablename){
  	this();
    froms.addTable(tablename);
  }

  public Select(String tablename,Connection conn){
    this(conn);
    froms.addTable(tablename);
  }

  public void setTableName(String tablename){
    froms.addTable(tablename);
  }

  public void setTableName(String tablename,String alias){
    froms.clear();
    froms.addTable(tablename,alias);
  }

  public void setTable(Table table){
    froms.clear();
    froms.addTable(table);
  }

  public void setTableName(String tablename,String alias,String jointype){
    froms.clear();
    froms.addTable(tablename,alias,jointype);
  }

  public Table getTable(){
    return froms.toArray()[0];
  }

  public void clear(){
    columns.clear();
    froms.clear();
    wheres.clear();
    orderbys.clear();
    groupbys.clear();
    pstmtSQL=new StringBuilder();
  }


  public void parse(){
  	pstmtSQL=sqlDialect.parse(columns,froms,wheres,getDistinct(),offset,limit,orderbys,groupbys);
  	parameters=wheres.getParameters();
  }

	public String getSQL(){
  	StringBuilder stmtSql;
  	stmtSql=sqlDialect.getSQL(columns,froms,wheres,getDistinct(),offset,limit,orderbys,groupbys);
		return isFormatSql?formatSQL(stmtSql.toString()):stmtSql.toString();
	}

  public Select setTop(int number){
    return setRowsLimit(number);
  }

  public void setOriginSelect(ISelect select){
    this.originSelect = select;
  }

   public ISelect getOriginSelect(){
     return originSelect;
   }

	@Override public Select setRowsOffset(int startByZero){
		return setOffset(startByZero);
	}

  public int getRowsOffset(){
    return this.offset;
  }

	public Select setOffset(int offset){
		this.offset=offset;
		return this;
	}

	public Select setRowsLimit(int limit){
		this.limit=limit;
		return this;
	}

  public int getRowsLimit(){
    return this.limit;
  }

  public int getTop(){
    return this.limit;
  }

  public Connection getConnection(){
    return this._conn;
  }

  public void setConnection(Connection connection){
    this._conn=connection;
    sqlDialect=SQLAssistant.setDefaultDialect(this._conn);
  }

  private String getDistinct(){
    return isDistinct?"DISTINCT":"";
  }

  public void addEqualColumn(String fieldName,String anotherFieldname){
    this.wheres.addEqualColumn(this.createField(fieldName),this.createField(anotherFieldname));
  }

  public void addNotEqualColumn(String fieldName,String anotherFieldname){
    this.wheres.addNotEqualColumn(this.createField(fieldName),this.createField(anotherFieldname));
  }

  public void addEqualNull(String fieldName){
    this.wheres.addCondition(LogicFactory.nullCondition(this.createField(fieldName)));
  }

  public void addNotNull(String fieldName){
    this.wheres.addCondition(LogicFactory.notNullCondition(this.createField(fieldName)));
  }

  public void addCondition(String value, Object ...args) {
    wheres.addCondition(value, args);
  }

  public void addCondition(ILogic value) {
    wheres.addCondition(value);
  }

  public void removeCondition(ILogic condition) {
    wheres.removeCondition(condition);
  }

  public void addCondition(ILogic[] value) {
    wheres.addCondition(value);
  }

  public void addEqualCondition(String fieldname,Object value){
    wheres.addEqualCondition(this.createField(fieldname),value);
  }

  public void addEqualCondition(String fieldName,boolean value){
    wheres.addEqualCondition(this.createField(fieldName),Boolean.valueOf(value));
  }

	public Select eq(String fieldName,String value){
		wheres.addEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select eq(String fieldName,Boolean value){
		wheres.addEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select eq(String fieldName,Number value){
		wheres.addEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select eq(String fieldName,Object value){
		wheres.addEqualCondition(this.createField(fieldName),value);
		return this;
	}

   public Select eq(String fieldName,Field field){
     wheres.addEqualCondition(this.createField(fieldName),field);
     return this;
   }

	/**大于*/
	public Select gt(String fieldName,Object value){
		wheres.addCondition(this.createField(fieldName),Where.MORE,value);
		return this;
	}

	/**大于等于*/
	public Select gteq(String fieldName,Object value){
		wheres.addCondition(this.createField(fieldName),Where.MOREEQUAL,value);
		return this;
	}

	/**小于*/
	public Select lt(String fieldName,Object value){
		wheres.addCondition(this.createField(fieldName),Where.LESS,value);
		return this;
	}

	/**小于等于*/
	public Select lteq(String fieldName,Object value){
		wheres.addCondition(this.createField(fieldName),Where.LESSEQAL,value);
		return this;
	}

	public Select notEq(String fieldName,String value){
		wheres.addNotEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select notEq(String fieldName,Object value){
		wheres.addNotEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select notEq(String fieldName,Number value){
		wheres.addNotEqualCondition(this.createField(fieldName),value);
		return this;
	}

	public Select notEq(String fieldName,Boolean value){
		wheres.addNotEqualCondition(this.createField(fieldName),value);
		return this;
	}

  public Select addNotBetween(String fieldName,Number minvalue,Number maxvalue){
    wheres.addCondition(new Between(this.createField(fieldName),minvalue,maxvalue,true));
    return this;
  }

  public Select addBetween(String fieldName,Number minvalue,Number maxvalue){
    wheres.addCondition(new Between(this.createField(fieldName),minvalue,maxvalue));
    return this;
  }

  public Select addNotBetween(String fieldName,Date minvalue,Date maxvalue){
    wheres.addCondition(new Between(this.createField(fieldName),minvalue,maxvalue,true));
    return this;
  }

  public Select addBetween(String fieldName,Date minvalue,Date maxvalue){
    wheres.addCondition(new Between(this.createField(fieldName),minvalue,maxvalue));
    return this;
  }

  public void addEqualCondition(String fieldName,String value){
    wheres.addEqualCondition(this.createField(fieldName),value);
  }

  public void addEqualCondition(String fieldName,Number value){
    wheres.addEqualCondition(this.createField(fieldName),value);
  }

  public void addEqualCondition(String fieldName,Date value){
    wheres.addEqualCondition(this.createField(fieldName),value);
  }

  public void addEqualCondition(String fieldName,java.sql.Date value){
    wheres.addEqualCondition(this.createField(fieldName),value);
  }

  public void addEqualCondition(String fieldName,Boolean value){
    wheres.addEqualCondition(this.createField(fieldName),value);
  }

  public void addCondition(String fieldName,String expression,Object value){
    wheres.addCondition(this.createField(fieldName),expression,value);
  }

  public void addLikeCondition(String fieldName,String value){
    this.wheres.addLikeCondition(this.createField(fieldName),value);
  }

	/**支持多个or条件的条件表达式,注意输入时参数顺序和类型不要弄错*/
	public Select orExpress(String expression,Object...args){
		ILogic[] conditions=new ILogic[args.length/2];
		for(int i=0,n=conditions.length;i<n;i++)
			conditions[i]=WhereFactory.create(createField((String)args[i*2]),expression,args[i*2+1]);
		this.addCondition(LogicFactory.or(conditions));
		return this;
	}

	/**支持多个or条件的条件表达式,自动判断参数是内容或表达式*/
	public Select or(Object...args){
		String fieldName=null,expression=Expression.EQ;
		Object rightValue=null;
		boolean isFieldValue=true;
		ArrayList<ILogic> conditionList=new ArrayList<ILogic>(args.length/2);
		for(Object arg:args) {
			if(isFieldValue) {
				fieldName=(String)arg;
				isFieldValue=false;
			}else if(arg instanceof String str && esdk.str.existOf(Expression.ALL,str,true)) {
				expression=(String)arg;
			}else {
        ILogic condition=null;
				rightValue=arg;
        if(rightValue!=null && rightValue instanceof ISelect select) {
          if(rightValue instanceof AbstractSelect as) {
            if(as.getColumns().size()==0 || as.isOutputAllFields())
              as.setColumns(as.hasColumn(fieldName)?fieldName:as.getPrimaryKeyFieldName());
          }
          condition=LogicFactory.in(createField(fieldName),select);
        }else{
          if(rightValue==null){
            expression=esdk.obj.or(expression,Expression.IS);
          }
          else if(rightValue.getClass().isArray()){
            expression=Expression.IN;
          }
          condition=WhereFactory.create(createField(fieldName),expression,rightValue);
        }
        conditionList.add(condition);
        isFieldValue=true;
        expression=Expression.EQ;
			}
		}
		this.addCondition(LogicFactory.or(esdk.array.toArray(conditionList,ILogic.class)));
		return this;
  }

  /**支持多个and条件的条件表达式,自动判断参数是内容或表达式*/
  public Select and(Object...args){
    String fieldName=null,expression=Expression.EQ;
    Object rightValue=null;
    boolean isFieldValue=true;
    ArrayList<ILogic> conditionList=new ArrayList<ILogic>(args.length/2);
    for(Object arg:args) {
      if(isFieldValue) {
        fieldName=(String)arg;
        isFieldValue=false;
      }else if(arg instanceof String str && esdk.str.existOf(Expression.ALL,str,true)) {
        expression=(String)arg;
      }else {
        ILogic condition=null;
        rightValue=arg;
        if(rightValue!=null && rightValue instanceof ISelect) {
          ISelect select=(ISelect)rightValue;
          if(rightValue instanceof AbstractSelect) {
            AbstractSelect as=(AbstractSelect)rightValue;
            if(as.getColumns().size()==0 || as.isOutputAllFields())
              as.setColumns(as.hasColumn(fieldName)?fieldName:as.getPrimaryKeyFieldName());
          }
          condition=LogicFactory.in(createField(fieldName),select);
        }else{
          if(rightValue==null){
            expression=esdk.obj.or(expression,Expression.IS);
          }
          else if(rightValue.getClass().isArray()){
            expression=Expression.IN;
          }
          condition=WhereFactory.create(createField(fieldName),expression,rightValue);
        }
        this.addCondition(condition);
        isFieldValue=true;
        expression=Expression.EQ;
      }
    }
    return this;
  }

  /**and表达式，直接写sql*/
  public Select and(String literalSql){
    this.addCondition(new Condition("("+literalSql+")").setAnd());
    return this;
  }

  /**多个字段的or条件的模糊查询，例如:where name like abc% or code like abc% */
  public void addLikeOrConditions(String[] fieldNames,String keyword){
		/*String keyword=fieldNames[fieldNames.length-1];
		String[] fieldNames=esdk.str.remove(fieldNames,keyword);*/
  	Field[] fields=new Field[fieldNames.length];
  	for(int i=0;i<fieldNames.length;i++) {
  		fields[i]=this.createField(fieldNames[i]);
  	}
    this.wheres.addCondition(LogicFactory.likeOr(fields,keyword));
  }

   public void addSmartBetweenOr(String[] fieldNames,Comparable min,Comparable max){
     Field[] fields=new Field[fieldNames.length];
     for(int i=0;i<fieldNames.length;i++) {
       fields[i]=this.createField(fieldNames[i]);
     }
     this.wheres.addCondition(LogicFactory.smartBetweenOr(fields,min,max));
   }

   public void addSmartBetween(String fieldName,Comparable min,Comparable max){
     this.wheres.addCondition(LogicFactory.smartBetween(this.createField(fieldName),min,max));
   }

   public void addNotSmartBetween(String fieldName,Comparable min,Comparable max){
     this.wheres.addCondition(LogicFactory.smartBetween(this.createField(fieldName),min,max).not());
   }

   public void addNotLikeCondition(String fieldName,String value){
    this.wheres.addNotLikeCondition(this.createField(fieldName),value);
  }

  public void removeCondition(String fieldName){
    wheres.removeCondition(this.createField(fieldName));
  }

  public void clearCondition(){
    wheres.clearCondition();
  }

  public void clearOn(){
    getTable().onCondition.clear();
  }

  public void addNotEqualCondition(String fieldName,Object value){
    wheres.addNotEqualCondition(this.createField(fieldName),value);
  }

  public void addNotEqualCondition(String fieldName,String value){
    wheres.addNotEqualCondition(this.createField(fieldName),value);
  }

  public void addNotEqualCondition(String fieldName,Boolean value){
    wheres.addNotEqualCondition(this.createField(fieldName),value);
  }

  public void addNotEqualCondition(String fieldName,Number value){
    wheres.addNotEqualCondition(this.createField(fieldName),value);
  }

  public void addEqualNumeric(String fieldName,String value){
    wheres.addEqualNumeric(this.createField(fieldName),value);
  }

  public void addNotEqualNumeric(String fieldName,String value){
    wheres.addNotEqualNumeric(this.createField(fieldName),value);
  }

  public void addInNumeric(String fieldName,String[] values){
    wheres.addInNumeric(this.createField(fieldName),values);
  }

  public void addInCondition(String fieldName,List<?> values){
      addInCondition(fieldName,esdk.array.toArray(values));
  }

  public void addInCondition(String fieldName,Object[] values){
    if(esdk.obj.isBlank(values))
      values=new String[]{null};
    if(values instanceof String[])
      wheres.addInCondition(this.createField(fieldName),(String[])values);
    else if(values instanceof Number[] numbers)
      wheres.addInCondition(this.createField(fieldName),numbers);
    else if(values instanceof Date[] dates)
      wheres.addInCondition(this.createField(fieldName),dates);
    else if(values instanceof Time[])
      wheres.addInCondition(this.createField(fieldName),(Time[])values);
    else
      throw new RuntimeException("不能识别的对象类型:"+values.getClass().getName());
  }

  public void addInCondition(String fieldName,Number... values){
    wheres.addInCondition(this.createField(fieldName),values);
  }

  public void addInCondition(String fieldName,String... values){
    wheres.addInCondition(this.createField(fieldName),values);
  }

  public void addInCondition(String fieldName,ISelect select){
    wheres.addInCondition(this.createField(fieldName),select);
  }

  public void addNotInCondition(String fieldName,ISelect select){
    wheres.addNotInCondition(this.createField(fieldName),select);
  }

  public void addNotInCondition(String fieldName,String... values){
    wheres.addNotInCondition(this.createField(fieldName),values);
  }

  public void addNotInCondition(String fieldName,Number... values){
    wheres.addNotInCondition(this.createField(fieldName),values);
  }

  public void addExists(ISelect select){
    wheres.addExists(select);
  }

  public void addNotExists(ISelect select){
    wheres.addNotExists(select);
  }

  public void addEqualEmplyValue(String fieldName){
    wheres.addEqualEmplyValue(this.createField(fieldName));
  }

  public void addEqualTrue(String fieldName){
    wheres.addEqualTrue(this.createField(fieldName));
  }

  public void addEqualEmplyNumeric(String fieldName){
    wheres.addEqualEmplyNumeric(this.createField(fieldName));
  }

  public void addNotEqualEmplyNumeric(String fieldName){
    wheres.addNotEqualEmplyNumeric(this.createField(fieldName));
  }

  public void addNotEqualEmplyValue(String fieldName){
    wheres.addNotEqualEmplyValue(this.createField(fieldName));
  }

  public boolean isDistinct(){
    return isDistinct;
  }

  public Select setDistinct(boolean isdistinct){
    this.isDistinct=isdistinct;
    return this;
  }

  public Select addIsNullColumn(String columnname,Object replaceValue){
    ISNULLColumn isnull=new ISNULLColumn(createField(columnname),replaceValue);
    columns.addColumn(isnull);
    return this;
  }

  public Select addIsNullColumn(String columnname,Object replaceValue,String aliasname){
    ISNULLColumn isnull=new ISNULLColumn(createField(columnname),replaceValue,aliasname);
    columns.addColumn(isnull);
    return this;
  }

  public Select addColumn(Column column) {
    columns.addColumn(column);
    return this;
  }

  public Select addColumn(ISQL select,String aliasName) {
    columns.addColumn(new SelectColumn(select,aliasName));
    return this;
  }

  public Select addColumn(String columnname){
    columns.addColumn(getTable(),columnname);
    return this;
  }

  public Select addColumnWithAlias(String columnname,String aliasname){
    columns.addColumn(getTable(),columnname,null,aliasname);
    return this;
  }

  public Select addAllColumns(){
    columns.addColumn(getTable(),"*");
    return this;
  }

  public Select setAllColumns(){
    columns.clear();
    columns.addColumn(getTable(),"*");
    return this;
  }

  public Select setColumns(String... columnNames){
    columns.clear();
    for(int i=0;i<columnNames.length;i++){
      columns.addColumn(getTable(),columnNames[i]);
    }
    return this;
  }

   public Select clearColumns(){
     columns.clear();
     return this;
   }

   public OrderBys getOrderbys(){
     return this.orderbys;
   }

   public void setOrderby(OrderBy ...orderbys){
     this.orderbys.addOrderBy(orderbys);
   }

  public boolean isOutputAllColumns() {
  	return columns.columnSet.containsValue("*");
  }

  public Columns getColumns() {
  	return this.columns;
  }

  public Select setColumns(String columnnames){
    setColumns(columnnames.split(","));
    return this;
  }

  public Select setColumnsAsJavaBean(String...columnNames){
		Column[] columns=new Column[columnNames.length];
		for(int i=0;i<columnNames.length;i++){
			columns[i]=new Column(this.getTable().createField(columnNames[i]),null,EasyStr.toCamelCase(columnNames[i]));
		}
		setColumns(columns);
		return this;
	}

  public Select addColumnsAsJavaBean(String...columnNames){
		Column[] columns=new Column[columnNames.length];
		for(int i=0;i<columnNames.length;i++){
			columns[i]=new Column(this.getTable().createField(columnNames[i]),null,EasyStr.toCamelCase(columnNames[i]));
		}
		addColumns(columns);
		return this;
	}

  public Select addColumns(Column... cols){
  	columns.addColumns(cols);
  	return this;
  }

  public Select setColumns(Column... cols){
  	columns.clear();
  	columns.addColumns(cols);
  	return this;
  }

  public Select addColumns(String... columnnames){
    Column[] columnArray=new Column[columnnames.length];
    for(int i=0;i<columnnames.length;i++){
      columnArray[i]=new Column(this.createField(columnnames[i]));
    }
    columns.addColumns(columnArray);
    return this;
  }

  public Select addColumn(String columnname,String functionname){
    columns.addColumn(getTable(),columnname,functionname);
    return this;
  }

  public Select addColumn(String columnname,String functionname,String aliasname) {
    columns.addColumn(getTable(),columnname,functionname,aliasname);
    return this;
  }

  public Select addOrderBy(String... fieldname){
    for(int i=0;i<fieldname.length;i++){
      addOrderBy(fieldname[i]);
    }
    return this;
  }

  public Select addOrderBy(String fieldname){
    if(fieldname.contains(" "))
      esdk.tool.throwException("排序字段名不能出现空格！");
    if(SQLAssistant.checkInjectSql(fieldname))
      esdk.tool.throwException("排序字段名存在sql注入风险！");
    this.orderbys.addOrderBy(this.createField(fieldname));
    return this;
  }

  public Select setOrderBy(String... fieldname){
  	clearOrderBy();
    for(int i=0;i<fieldname.length;i++){
      addOrderBy(fieldname[i]);
    }
    return this;
  }

  public Select addOrderBy(String fieldname,boolean isDesc){
    this.orderbys.addOrderBy(this.createField(fieldname),isDesc);
    return this;
  }

  /**清除order by语句*/
  public void clearOrderBy(){
  	this.orderbys.clear();
  }

  public Select setOrderBy(String fieldname,boolean isDesc){
  	clearOrderBy();
    this.orderbys.addOrderBy(this.createField(fieldname),isDesc);
    return this;
  }
  public Select addGroupBy(String fieldname){
    groupbys.add(this.createField(fieldname));
    return this;
  }

  public Select setGroupBy(String... fieldnames){
    groupbys.clear();
    for(int i=0;i<fieldnames.length;i++){
    	addGroupBy(fieldnames[i]);
    }
    return this;
  }

  public Select setHaving(String having) {
  	groupbys.setHaving(having);
  	return this;
  }

  public String getTableAliasName() {
    return getTable().getAliasName();
  }

  public Select setTableAliasName(String alias) {
    getTable().setAliasName(alias);
    return this;
  }

  public Field createField(String fieldname) {
    return getTable().createField(fieldname);
  }

  public Column createColumn(String fieldname,String aliasName) {
    return new Column(getTable().createField(fieldname),null,aliasName);
  }

  public Column createColumn(String columnName) {
    return new Column(getTable().createField(columnName));
  }

  public Select addOnCondition(Field leftfield,Field rightfield){
    getTable().addOnCondition(new OnCondition(leftfield,rightfield));
    return this;
  }

  public Select addOnCondition(Field field,String value){
    getTable().addOnCondition(new Where(field,Where.EQUAL,value));
    return this;
  }

  public Select addOnCondition(Field field,Object value){
    getTable().addOnCondition(new Where(field,Where.EQUAL,value));
    return this;
  }

  public Select addOnCondition(ILogic condition){
    getTable().addOnCondition(condition);
    return this;
  }

  public boolean executeQuery() throws SQLException{
    String sql=getSQL();
    try{
      Statement stmt=_conn.createStatement();
      _resultset=stmt.executeQuery(sql);
      return _resultset!=null;
    }
    catch(SQLException e){
      throw new SQLException(e.toString()+" SQL语句："+sql);
    }
  }

  public int perform() throws SQLException{
    if(_conn==null)
      throw new SQLException("Connection不能为空！请先设置setConnection(conn)");
    parse();
    PreparedStatement pstmt;
    try{ //默认就是FORWARD_ONLY和READ_ONLY
      pstmt=_conn.prepareStatement(pstmtSQL.toString(),ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
      for(int i=0;i<parameters.length;i++){
        pstmt.setObject(i+1,parameters[i]);
      }
      if(_showSql) {
        TimeMeter tm=TimeMeter.startNew();
        _resultset=pstmt.executeQuery();
        this.executeConcumingTime=tm.getElapse();
        if(!isShowFullSql)
          SQLAssistant.showSql(this);
      }
      else {
        _resultset=pstmt.executeQuery();
      }
      pstmt.closeOnCompletion();
      return 0;
    }
    catch(SQLException e){
      if(e.getMessage().equals("Query execution was interrupted, maximum statement execution time exceeded")&&retryTimes>0){
        esdk.error("可重试{}次Select失败，错误提示：{}。执行的SQL：{}",retryTimes,e.toString(),this.getSQL());
        retryTimes--;
        esdk.tool.sleep(this.retryIntervalMs);
        return perform();
      }
      throw new SQLRuntimeException(e.toString()+"。执行SQL:"+getSQL(),e);
    }
  }

   public Select retry(int retryTimes){
     this.retryTimes=retryTimes;
     return this;
   }

   public Select retry(int retryTimes,int retryIntervalMs){
     this.retryTimes=retryTimes;
     this.retryIntervalMs=retryIntervalMs;
     return this;
   }

  public boolean isExistRecord() throws SQLException{
    perform();
    boolean result=_resultset.next();
    EasySql.close(_resultset);
    return result;
  }

  public ABRow getFirstRow() throws SQLException{
    Map map=getFirstRecord();
    if(map==null)
      return null;
    else
      return new ABRow(map);
  }

  /**获取第一条记录的第一个字段值*/
  public Integer getFirstInteger() {
  	return getFirstValue(Integer.class);
  }

  /**获取第一条记录的第一个字段值，并指定默认值*/
  public Integer getFirstInteger(Integer defVal) {
  	return getFirstValue(Integer.class,defVal);
  }

  /**获取第一条记录的第一个字段值*/
  public String getFirstString() {
  	return getFirstValue(String.class);
  }

  /**获取第一条记录的第一个字段值，并指定默认值*/
  public Double getFirstDouble(Double defVal) {
  	return getFirstValue(Double.class,defVal);
  }

  /**获取第一条记录的第一个字段值*/
  public Double getFirstDouble() {
  	return getFirstValue(Double.class);
  }

  /**获取第一条记录的第一个字段值，并指定默认值*/
  public String getFirstLong(String defVal) {
  	return getFirstValue(String.class,defVal);
  }

  /**获取第一条记录的第一个字段值*/
  public Long getFirstLong() {
  	return getFirstValue(Long.class);
  }

  /**获取第一条记录的第一个字段值，并指定默认值*/
  public Long getFirstLong(Long defVal) {
  	return getFirstValue(Long.class,defVal);
  }

  /**获取第一条记录的第一个字段值，并指定默认值
   * @throws SQLException */
  public <V> V getFirstValue(Class<V> cls) {
  	ABRow row;
		try{
			row=getFirstRow();
			if(row==null)
	  		return null;
	  	else {
	  		String field=(String)row.record().keySet().iterator().next();
	  		return esdk.obj.valueOf(cls,row.get(field));
	  	}
		}catch(SQLException e){
			throw esdk.tool.wrapThrowble(e);

		}
  }

  /**获取第一条记录的第一个字段值*/
  public <V> V getFirstValue(Class<V> cls,V defVal) {
  	return esdk.obj.or(getFirstValue(cls),defVal);
  }

  private Map getFirstRecord() throws SQLException{
    perform();
    ABResultSet arset=new ABResultSet(_resultset);
    Map result=null;
    if(arset.next()){
      result=arset.getFirstRow().record();
    }
    arset.close();
    return result;
  }

  public ResultSet toResultSet() throws SQLException{
    perform();
    return _resultset;
  }

  public ABResultSet toABResultSet() throws SQLException{
    if(useSessionCache){
      ORMSession ormSession=ORMSessionFactory.getORMSession(_conn);
      return esdk.obj.or(ormSession.getABResultSet(this),()->ormSession.putABResultSet(this,new ABResultSet(toResultSet())));
    }
    else if(isCachable&&useCacheSec>0){
      String cacheKey=SQLAssistant.getCacheKey(this);
      return esdk.obj.or((ABResultSet)RedisUtils.getObj(cacheKey),()->RedisUtils.setObj(cacheKey,new ABResultSet(toResultSet()),useCacheSec));
    }
    else
      return new ABResultSet(toResultSet());
  }
  public List toList(IRowMappper mapper) throws SQLException{
  	perform();
  	List result=new ArrayList();
  	for(int i=0;_resultset.next();i++) {
  		result.add(mapper.mapRow(_resultset, i));
  	}
  	EasySql.close(_resultset);
  	return result;
  }

  public List toList(IRowMappper mapper,int start,int limit) throws SQLException{
  	perform();
  	List result=new ArrayList();
  	limit=limit<0?Integer.MAX_VALUE:limit;
  	for(int i=0,n=start+limit;i<n&&_resultset.next();i++) {
  		if(i>=start)
  			result.add(mapper.mapRow(_resultset, i));
  	}
  	EasySql.close(_resultset);
  	return result;
  }

  public List toList(final Class pojoClass,int start,int limit) throws SQLException{
  	return toList(new ReflectRowMapper(pojoClass),start,limit);
  }

  public List toList(final Class pojoClass) throws SQLException{
  	return toList(pojoClass,0,-1);
  }

  @Override
  public ABRowSet toRowSet() throws SQLException {
    if(useSessionCache){
      ORMSession ormSession=ORMSessionFactory.getORMSession(_conn);
      return esdk.obj.or(ormSession.getABRowSet(this),()->{
        ABResultSet abResultSet = toABResultSet();
        abResultSet.setSelect(this);
        return ormSession.putABRowSet(this,new ABRowSet(abResultSet));
      });
    }
    ABResultSet abResultSet = toABResultSet();
    abResultSet.setSelect(this);
  	return new ABRowSet(abResultSet);
	}

  @Override
  public Object[][] toArray2()throws SQLException{
  	return toArray2(true);
  }

  @Override
  public Object[][] toArray2(boolean isOutputColumn,String... columns) throws SQLException{
    ResultSet rs=toResultSet();
    Object[][] result=isOutputColumn?EasySql.resultSetToArrWithHeader(rs,columns):EasySql.rsToArr2(rs,columns);
    EasySql.close(rs);
    return result;
  }

  public JSONArray toJsonArray() throws SQLException{
  	return toJsonArray(true);
  }

  public JSONArray toJsonArray(boolean isFormatJavaBeanName) throws SQLException{
		return toABResultSet().toJsonArray(isFormatJavaBeanName);
	}

  public void close() {
    this.columns.clear();
    this.columns=null;
    this.froms.clear();
    this.froms=null;
    this.groupbys.clear();
    this.groupbys=null;
    this.orderbys.clear();
    this.orderbys=null;
    this.wheres.clear();
    this.wheres=null;
    this._conn=null;
  }

  public String getTableName(){
    return getTable().getTableName();
  }

  @Override public String toString(){
    return getSQL();
  }

  @Override public int count() throws SQLException {
    if(originSelect != null) return originSelect.count();
  	int result;
  	int top=getTop();
  	setTop(0);
  	if(this.groupbys.size()==0&&this.getDistinct().isBlank()) {
	  	Columns cols=this.columns;
	  	this.columns=new Columns();
	  	OrderBys orders=this.orderbys;
	  	this.orderbys=new OrderBys();
	  	addColumns(new Column("","1",Column.COUNT,"TOTAL"));
	  	ABResultSet abrs=toABResultSet();
	  	result=abrs.absolute(0)?abrs.getFirstRow().getInteger("TOTAL"):0;
	  	abrs.close();
	  	this.columns=cols;
	  	this.orderbys=orders;
  	}
  	else {
  		result=new WrapSelect(this,"a").useCache(useCacheSec).count();
  	}
  	setTop(top);
  	return result;
  }

	public Select setJoinType(String join){
		Table table=froms.getFirst();
		table.setRelationShip(join);
		if(EasyStr.isBlank(table.getAliasName()))
			table.createAliasName();
		return this;
	}

  public int queryForInt() throws SQLException{
  	ResultSet rs=toResultSet();
  	int result=0;
  	if(rs.next())
  		result=rs.getInt(1);
  	else
  		result=0;
  	EasySql.close(rs);
  	return result;
  }

  public double queryForDouble() throws SQLException{
  	ResultSet rs=toResultSet();
  	double result=0;
  	if(rs.next())
  		result=rs.getDouble(1);
  	else
  		result=0;
  	EasySql.close(rs);
  	return result;
  }

  public Object queryForObject() throws SQLException{
  	ResultSet rs=toResultSet();
  	Object result=null;
  	if(rs.next())
  		result=rs.getDouble(1);
  	EasySql.close(rs);
  	return result;
  }

  public void setFormatSQL(boolean isformatsql) {
  	this.isFormatSql=isformatsql;
  }

  public String formatSQL(String sql) {
  	return sql.replaceAll("\\r?\\n"," ");
  }

  @Override public Select showSql(boolean isShowSql){
		this._showSql=isShowSql;
		return this;
	}

  @Override public boolean showSql(){
     return this._showSql;
  }

  public Select showFullSql(boolean b){
    this.isShowFullSql=b;
    return this;
  }

  public Select useCache(boolean ifUseCache) {
    if(ifUseCache && this.useCacheSec==Constant.ResetUseCacheSec){
      this.useCacheSec=Constant.DefaultCacheSec;
    }
    this.useCache(ifUseCache?this.useCacheSec:Constant.ResetUseCacheSec);
    return this;
  }

	public Select useCache(int sec) {
		this.useCacheSec=sec;
		return this;
	}

  public Select useSessionCache(boolean useSessionCache){
    this.useSessionCache=useSessionCache;
    return this;
  }

	public Select clone() {
		Select result=new Select();
		result._conn=this._conn;
	  result.columns=this.columns.clone();
    result.froms=this.froms.clone();
    result.wheres=this.wheres.clone();
    result.orderbys=this.orderbys.clone();
    result.groupbys=this.groupbys.clone();
    result.offset=this.offset;
    result.limit=this.limit;
    result.retryTimes=this.retryTimes;
    result.retryIntervalMs=this.retryIntervalMs;
    return result;
	}

  public Wheres wheres(){
    return wheres;
  }

  @Override public long getExecuteConsuming(){
    return executeConcumingTime;
  }

}
